Snowflakeにログインする際のパスワード周りに任意の制約をかけることができる「Custom Password Policy」を試してみた #SnowflakeDB

※本エントリは、Snowflakeをもっと使いこなそう！ Advent Calendar 2022の１３日目の記事となります。

さがらです。

Snowflakeにログインする際のパスワード周りに任意の制約をかけることができるCustom Password Policyを試してみたので、その内容をまとめてみます。

Custom Password Policyとは

まず、「パスワードポリシー」について簡単に触れておきます。一般的に、ユーザーとしてログインが必要なサイトやアプリケーションでは、ログインする際に必ずユーザーIDとパスワードを入力すると思います。なので新規のユーザー登録時にパスワードの設定は必須となります。

このパスワードの設定のとき、「最低８文字以上」「大文字小文字をそれぞれ１文字以上含めること」みたいな制約のもとにパスワードを設定するように指示されたことはないでしょうか？この制約が「パスワードポリシー」です。

そして、Snowflakeでも１人につき１ユーザーを作る必要があるため、各ユーザーごとににパスワードの設定が必要です。このとき、Snowflakeにも実はパスワードポリシーが設定されています。以下は公式Docからの引用ですが、このようなポリシーとなっています。

• Must be at least 8 characters long.（少なくとも8文字の長さでなければなりません）
• Must contain at least 1 digit.（少なくとも 1 桁の数字が含まれている必要があります。）
• Must contain at least 1 uppercase letter and 1 lowercase letter.（少なくとも 1 つの大文字と 1 つの小文字を含める必要があります。）

しかし、企業によっては「独自のパスワードポリシーを定義したい」「パスワードを定期的に変更するように促したい」といった要望があると思います。

そんな要望に応えることが出来る機能が、Custom Password Policyです！

2022年11月にGAとなった機能で、「パスワードに特殊文字を～個入れること」「～日経過したらパスワードの変更を必須とすること」「ロックアウトするまでの試行回数は～回とすること」のように、非常に細かくパスワードポリシーが設定可能です。

詳しくは下記の公式Docも併せてご覧ください。

試してみた

では、実際に公式Docに沿って、Custom Password Policyを定義してみます！

事前準備

まず、ドキュメントには記載がないのですが、Custom Password Policyは「スキーマレベルのオブジェクト」のため、専用のデータベースとスキーマを作成しておきます。普段データを貯めて管理しているデータベースやスキーマと同じ所で管理してしまうと、どこにCustom Password Policyを定義したかわからなくなってしまうリスクもあるため、個人的には専用のデータベースとスキーマを定義することを推奨します。

ここでは、データベースをsecurity、スキーマをpolicies、として作成しておきます。

use role sysadmin;
create database security;

use database security;
create schema policies;

続いて、ポリシーを管理するpolicy_adminというカスタムロールを作成します。

use role useradmin;

create role policy_admin;

続いて、Custom Password Policyを作成するための権限を付与します。

use role securityadmin;

grant usage on database security to role policy_admin;

grant usage on schema security.policies to role policy_admin;

grant create password policy on schema security.policies to role policy_admin;

grant apply password policy on account to role policy_admin;

show grants to role policy_admin; -- 正しく付与されているか権限確認

次に、作成したロールpolicy_adminを対象ユーザーに付与します。

use role securityadmin;
grant role policy_admin to user satoshisagara;

Custom Password Policyの作成

事前準備ができたので、Custom Password Policyの作成に移ります！実際には、create password policyというコマンドを実行して、作成します。

下記のクエリを実行して、Custom Password Policyを作成します。

use role policy_admin;

use schema security.policies;

create password policy password_policy_prod_1
    password_min_length = 12 -- パスワードに必要な最低文字数
    password_max_length = 24 -- パスワードに設定可能な最大文字数
    password_min_upper_case_chars = 2 -- パスワードに必要な大文字の最小数
    password_min_lower_case_chars = 2 -- パスワードに必要な小文字の最大数
    password_min_numeric_chars = 2 -- パスワードに必要な数字の数
    password_min_special_chars = 2 -- パスワードに必要な特殊文字（!や+など）の数
    password_max_age_days = 30 -- パスワードの変更が必要となる日数の指定
    password_max_retries = 3 -- ロックアウトされるまでにパスワードの入力を試行できる回数
    password_lockout_time_mins = 30 -- 一度ロックアウトした後、何分間ロックされるのかを指定
    comment = 'production account password policy';

Custom Password Policyを対象のSnowflakeアカウントへ適用

作成したCustom Password Policyを、ALTER ACCOUNTコマンドを使ってアカウントレベルに適用します。 （ちなみに、ALTER USERコマンドでユーザーレベルに適用することも可能です。アカウントレベルとユーザーレベル、どちらにもポリシーが適用されている場合にはユーザーレベルに適用しているポリシーが優先されます。）

alter account set password policy security.policies.password_policy_prod_1;

新しいユーザーを招待してCustom Password Policyが適用されるか確かめてみる

では、このSnowflakeアカウントで新しいユーザーを招待して、Custom Password Policyが適用されるか確かめてみます。

まず、SnowsightのUIから下図のように設定してユーザーを招待します。この時に設定するパスワードは一時的なもののため、Custom Password Policyは適用されていません。

そして、この招待したユーザーでログインしようとすると、このように「パスワードの期限切れ」という画面が出てきてパスワードの変更を促されます。

ここで見ていただきたいのが、下図の赤枠内です。先ほど作成したCustom Password Policyの条件が適用されていることがわかります！

おまけ：既存ユーザーへのパスワード変更を強制する

ちなみに、Custom Password Policyを適用しただけでは、既存のユーザーへのパスワード変更を強制することはできません。

もし、適用したCustom Password Policyを矯正させたい場合には、ALTER USERコマンドを使って、must_change_password = trueとすればOKです。（下記は実行するコマンドのサンプル）

alter user jsmith set must_change_password = true;

最後に

Snowflakeにログインする際のパスワード周りに任意の制約をかけることができるCustom Password Policyを試してみました。

パスワードの運用ルールが企業内に厳格に存在する場合、とても役立つ機能かと思います。ぜひ、お試しください！